if exists (select 1 from sysobjects where name = 'get_arbeitsplandata' and type = 'P')
begin
   drop procedure get_arbeitsplandata
   print 'Procedure: get_arbeitsplandata deleted ...'
end
go
create procedure get_arbeitsplandata(
  @planid     int = 1
)
as
begin
  set nocount on

  create table #plan
  (
         prsid             int,
         prsschemaid       int,
         nachname          varchar(32),
         vorname           varchar(32),
         prozent           int,
         abteilungid       int,
         rgb               int,
         colorname         varchar(32),
         abteilungname     varchar(32),
         abteilungbeschrid int,
         persontyp         int
  )

create table #abtschema
(
     abteilungid int,
     prsschemaid int
)

  insert #plan
  select prsid             = isnull(ps.PrsID,0),--isnull(p.prsid,0),
         prsschemaid       = PrsSchemaID,
         nachname          = isnull(p.Nachname,''),
         vorname           = isnull(p.Vorname, ''),
         prozent           = isnull(nullif(pa.Prozent,0) ,isnull(a.Prozent,0)),--isnull(a.prozent,0),--
         abteilungid       = ps.AbteilungID,
         rgb               = ps.RGB,
         colorname         = ps.ColorName,
         abteilungname     = abt.Name,
         abteilungbeschrid = abt.BeschrID,
         persontyp         = isnull(p.PersonTyp,1) --1 --Stammperson

    from ArbeitsPlan ap
   inner join Schemas s
      on ap.SchemaID = s.SchemaID
   inner join PlanSchema ps
      on ps.SchemaID = s.SchemaID
    left outer join Person p
      on p.PrsId = ps.PrsID
     and p.PersonTyp = 1
    left outer join Arbeitspensum a 
      on a.PrsID = p.PrsId 
--     and a.status = 1
     and (a.Von <= ap.Von and a.Bis >= ap.Bis)
--(select max(ab.bis) from arbeitspensum ab where ab.prsid = p.prsid)
    left outer join PersonalAbteilung pa
      on pa.PrsId = p.PrsId
     and pa.AbteilungID = ps.AbteilungID
     and pa.Bis = (select max(pa2.Bis) from PersonalAbteilung pa2 where pa2.PrsId = p.PrsId and pa.AbteilungID = ps.AbteilungID)
     and (pa.Von <= ap.Von and pa.Bis >= ap.Bis)
    left outer join Abteilung abt
      on abt.AbteilungID = ps.AbteilungID
   where ap.PlanID = @planid
   order by prsschemaid

  insert #plan
  select prsid             = isnull(p.PrsId,0),
         prsschemaid       = 0,--noch machen...
         nachname          = isnull(p.Nachname,''),
         vorname           = isnull(p.Vorname, ''),
         prozent           = 0,
         abteilungid       = apz.abteilungID,
         rgb               = -1,
         colorname         = 'White',
         abteilungname     = abt.Name,
         abteilungbeschrid = abt.BeschrID,
         persontyp         = p.PersonTyp--2 --Zusatzperson
    from ArbeitsPlan ap
   inner join ArbeitsPlanzprs apz
      on apz.planID = ap.PlanID
--    left outer join zusatzperson zp
--      on zp.zprsid = apz.zprsid
    left outer join Person p
      on p.PrsId = apz.zprsID
     and p.PersonTyp = 2
    left outer join Abteilung abt
      on abt.AbteilungID = apz.abteilungID
   where ap.PlanID = @planid

 declare @prsschemaid int
  select @prsschemaid = max(prsschemaid) from #plan

  update #plan 
     set prsschemaid = @prsschemaid +1 
   where persontyp = 2

  select * from #plan
   order by abteilungid, prsschemaid, prsid

end
go
print 'Procedure: get_arbeitsplandata done ...'
go

grant exec on get_arbeitsplandata to prsadmins with grant option
go
grant exec on get_arbeitsplandata to prsusers
go

